﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Media.Imaging;
using System.Globalization;
using System.Collections.ObjectModel;
using System.Diagnostics;

namespace University.Workshop.DataAccess
{
    public class DatabaseQuery
    {
        private string connect;
        private User user;
        
        public DatabaseQuery()
        {
            connect = "Data Source=.\\sqlexpress; Initial Catalog=Pokracho; Integrated Security=true";
        }

        public bool ChangePassword(string userName,string newPassword)
        {
            bool exit = true;
            
            using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [User] Where userName = @username";
                    SqlCommand change = SQL_Connection.CreateCommand();
                    change.CommandText = "UPDATE [User] SET Password = @newpass Where userName = @userna";
                    change.Parameters.AddWithValue("newpass", newPassword);
                    change.Parameters.AddWithValue("userna", userName);
                    cmd.Parameters.AddWithValue("username", userName);
                    SQL_Connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            reader.Close();
                                change.ExecuteNonQuery();
                        }
                    } 
                    else
                        exit = false;
                    
                }
                catch (SqlException)
                {
                    //throw new DataAccessException("Error getting a user", ex);
                }
                return exit;
            }
        }
        public bool LoanDelete(string name)
        {
            bool result = false;
            SqlConnection SQL_Connection = null;
            {
                try
                {
                    SQL_Connection = new SqlConnection(this.connect);
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "DELETE FROM [ServerLoan] WHERE UserName=@username";
                    cmd.Parameters.AddWithValue("username", name);
                    SQL_Connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Close();
                    result = true;
                }
                catch (SqlException)
                {
                    return false;
                }
                finally
                {
                    if (SQL_Connection != null)
                    {
                        SQL_Connection.Dispose();
                    }
                }

            }
            return result;
        }
        public bool LoanSetUser(string name,string loan)
        {
            bool result = false;
            SqlConnection SQL_Connection = null;
            {
                try
                {
                    SQL_Connection = new SqlConnection(this.connect);
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [ServerLoan] (UserName,Loan) VALUES(@username,@loan)";

                    cmd.Parameters.AddWithValue("username", name);
                    cmd.Parameters.AddWithValue("loan",loan);
                    SQL_Connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Close();
                    result = true;
                }
                catch (SqlException)
                {
                    return false;
                }
                finally
                {
                    if (SQL_Connection != null)
                    {
                        SQL_Connection.Dispose();
                    }
                }


            }
            return result;
        }
        public string LoanStatusUser(string name)
        {
            string Result = "no";
            SqlConnection SQLConnection = null;
            try
            {
                SQLConnection = new SqlConnection(this.connect);
                SqlCommand command = SQLConnection.CreateCommand();
                command.CommandText = "SELECT * FROM [ServerLoan] WHERE UserName=@name";
                command.Parameters.AddWithValue("@name", name);

                SQLConnection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    if (reader.Read())
                        Result = reader["Loan"].ToString();
                }
            }
            catch (SqlException)
            {
                Result = "no";
            }
            finally
            {
                if (SQLConnection != null)
                {
                    SQLConnection.Close();
                }
            }
            return Result;
        }
        public bool RemoveDeck(string name, int newDeck)
        {
            bool result = false;
            SqlConnection SQL_Connection = null;
            {             
                    try
                    {
                        SQL_Connection = new SqlConnection(this.connect);
                        SqlCommand cmd = SQL_Connection.CreateCommand();
                        cmd.CommandText = "DELETE FROM [Deck] WHERE UserName=@username AND Deck=@deck";
                        //DELETE FROM [pokracho].[dbo].[Deck] WHERE UserName = 'garcia21' AND Deck = 3
                        cmd.Parameters.AddWithValue("username", name);
                        cmd.Parameters.AddWithValue("deck", newDeck);
                        SQL_Connection.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        reader.Close();
                        result = true;
                    }
                    catch (SqlException)
                    {
                        return false;
                    }
                    finally
                    {
                        if (SQL_Connection != null)
                        {
                            SQL_Connection.Dispose();
                        }
                    }
                
            }
            return result;
        }
        public bool SetDeck(string name, int newDeck)
        {
            bool result = false;
            SqlConnection SQL_Connection = null;
            {             
                    try
                    {
                        SQL_Connection = new SqlConnection(this.connect);
                        SqlCommand cmd = SQL_Connection.CreateCommand();
                        cmd.CommandText = "INSERT INTO [Deck] (Deck,UserName) VALUES(@deck,@username)";

                        cmd.Parameters.AddWithValue("username", name);
                        cmd.Parameters.AddWithValue("deck", newDeck);
                        SQL_Connection.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        reader.Close();
                        result = true;
                    }
                    catch (SqlException)
                    {
                        return false;
                    }
                    finally
                    {
                        if (SQL_Connection != null)
                        {
                            SQL_Connection.Dispose();
                        }
                    }

                
            }
            return result;
        }
        public string GetDeck(string name)
        {
            int Result = 0;
            string ResultDeck = "no";
            SqlConnection SQLConnection = null;
            try
            {
                SQLConnection = new SqlConnection(this.connect);
                SqlCommand command = SQLConnection.CreateCommand();
                command.CommandText = "SELECT * FROM [Deck] WHERE UserName=@name";
                command.Parameters.AddWithValue("@name", name);

                SQLConnection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    for (int i = 0; i < 3; i++)
                    {
                        if (Result == 0)
                        {
                            Result++;
                            if (reader.Read())
                                ResultDeck = reader["Deck"].ToString();
                        }
                        else
                        {
                            Result++;
                            if (reader.Read())
                                ResultDeck = ResultDeck + " " + reader["Deck"].ToString();
                        }

                    }
                }
            }
            catch (SqlException)
            {
                Result = -1;
            }
            finally
            {
                if (SQLConnection != null)
                {
                    SQLConnection.Close();
                }
            }
            return ResultDeck;
        }
        // 1111111111111111111
        public bool SetChips(string name, long newChips)
        {
            int Result;
            SqlConnection SQLConnection = null;
            {
                try
                {
                    SQLConnection = new SqlConnection(this.connect);
                    SQLConnection.Open();
                SqlCommand command = SQLConnection.CreateCommand();
                command.CommandText = "UPDATE [User] SET Chips=@newChips WHERE UserName=@name";
                command.Parameters.AddWithValue("name", name);
                command.Parameters.AddWithValue("newChips", newChips);
                    
                    Result = command.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    Result = 0;
                }
                finally
                {
                    if (SQLConnection != null)
                    {
                        SQLConnection.Dispose();
                    }
                }
            }

            if (Result <= 0)
            {
                return (false);
            }
            return (true);
        }
        public bool SetLevelTimeWon(string name, int level, long timeWon)
        {
            int Result;
            SqlConnection SQLConnection = null;
            {
                try
                {
                    SQLConnection = new SqlConnection(this.connect);
                    SQLConnection.Open();
                    SqlCommand command = SQLConnection.CreateCommand();
                    command.CommandText = "UPDATE [User] SET UserLevel = @level,TimeWon = @timeWon WHERE UserName=@name";
                    command.Parameters.AddWithValue("name", name);
                    command.Parameters.AddWithValue("level",level);
                    command.Parameters.AddWithValue("timeWon", timeWon);

                    Result = command.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    Result = 0;
                }
                finally
                {
                    if (SQLConnection != null)
                    {
                        SQLConnection.Dispose();
                    }
                }
            }

            if (Result <= 0)
            {
                return (false);
            }
            return (true);
        }
        public long GetChips(string name)
        {
            long Result = -1;
             SqlConnection SQLConnection= null;
            try
            {
           
                SQLConnection= new SqlConnection(this.connect);
                SqlCommand command = SQLConnection.CreateCommand();
                command.CommandText = "SELECT * FROM [User] WHERE UserName=@name";
                command.Parameters.AddWithValue("@name", name);

                
                    SQLConnection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            Result = Convert.ToInt64(reader["Chips"].ToString(), CultureInfo.InvariantCulture);
                        }
                    }
                }
                catch (SqlException)
                {
                    Result = -1;
                }
                finally
                {
                    if (SQLConnection != null )
                    {
                        SQLConnection.Close();
                    }
                }
            

            return (Result);
        }

        public bool ChangeAmount(string numberCard, string amount, string operation, string name)
        {
            bool result = false;

            float auxiliary = GetAmount(numberCard, name);//getAmount(string cardNum, string name)
            float auxiliaryTwo = 0;
            float auxiliaryThree = Convert.ToInt64(amount,
                              CultureInfo.InvariantCulture);
            if (operation == "sum")
            {
                auxiliaryTwo = auxiliary + auxiliaryThree;
                result = true;
            }
            else
            {
                if (auxiliary >= auxiliaryThree)
                {
                    auxiliaryTwo = auxiliary - auxiliaryThree;
                    result = true;
                }
            }
            if (result)
            {
                using (SqlConnection sqlconnection = new SqlConnection(this.connect))
                {
                    try
                    {


                        SqlCommand cmd = sqlconnection.CreateCommand();
                        cmd.CommandText = "SELECT * FROM [CreditCard] where Code = @cardN ";
                        SqlCommand cmdTwo = sqlconnection.CreateCommand();
                        cmdTwo.CommandText = "UPDATE [CreditCard] SET Amount = @newAmount where Code = @numC";

                        cmd.Parameters.AddWithValue("cardN", numberCard);
                        cmdTwo.Parameters.AddWithValue("newAmount", auxiliaryTwo);
                        cmdTwo.Parameters.AddWithValue("numC", numberCard);

                        sqlconnection.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            if (reader.Read())
                            {
                                reader.Close();
                                cmdTwo.ExecuteNonQuery();
                            }
                        }
                        else
                            reader.Close();
                    }
                    catch (SqlException)
                    {
                        return false;
                    }
                }
            }
            return result;
        }
        //Send("41 " + numCard + " " + name+" "+amount);
        public bool LogOnUserCreditCard(string numberCard, string name)
        {
            bool result = false;
            using (SqlConnection sqlconnection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand cmd = sqlconnection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [CreditCard] where code = @cardN AND UserName = @nam";
                    cmd.Parameters.AddWithValue("cardN", numberCard);
                    cmd.Parameters.AddWithValue("nam", name);
                    sqlconnection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            result = true;
                        }
                    }
                }
                catch (SqlException)
                {
                   
                }
            }
            return result;
        }
        public void SetGameStatus(string name, string game, string time)
        { 
           SqlConnection SQLConnection = null;
           try
           {
               SQLConnection = new SqlConnection(this.connect);
               SqlCommand cmd = SQLConnection.CreateCommand();
               cmd.CommandText = "SELECT * FROM [MiniGame] Where UserName = @Uname AND Game=@mGame";
               cmd.Parameters.AddWithValue("Uname", name);
               cmd.Parameters.AddWithValue("mGame", game);
               SQLConnection.Open();
               SqlDataReader reader = cmd.ExecuteReader();
               if (reader.HasRows)
               {
                   reader.Close();

                   if (time == null)
                       throw new ArgumentNullException("time");
                   time = time.Replace('_',' ');
                   SqlCommand newCommand = SQLConnection.CreateCommand();
                   newCommand.CommandText = "UPDATE [MiniGame] SET Time = @time Where UserName = @Uname AND Game=@game";
                   newCommand.Parameters.AddWithValue("Uname",name);
                   newCommand.Parameters.AddWithValue("time",time);
                   newCommand.Parameters.AddWithValue("game",game);
                   newCommand.ExecuteNonQuery();
               }
           }
           catch (SqlException)
           {
            
           }
           finally
           {
               if (SQLConnection != null)
                   SQLConnection.Dispose();
           }
        }
        public bool GetGameStatus(string name, string game, string time)
        { 
            SqlConnection SQLConnection = null;
            try
            {
                SQLConnection = new SqlConnection(this.connect);
                SqlCommand cmd = SQLConnection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [MiniGame] Where UserName = @Uname AND Game=@mGame";
                cmd.Parameters.AddWithValue("Uname", name);
                cmd.Parameters.AddWithValue("mGame",game);
                SQLConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    reader.Close();
                    if (time == null)
                        throw new ArgumentNullException("time");
                    time=time.Replace('_',' ');
                    SqlCommand command = SQLConnection.CreateCommand();
                    command.CommandText = "INSERT INTO [MiniGame](UserName,Game,Time) VALUES(@Username,@game,@time)";
                    command.Parameters.AddWithValue("Username",name);
                    command.Parameters.AddWithValue("game", game);
                    command.Parameters.AddWithValue("time", time);
                    command.ExecuteNonQuery();
                    return true ;
                }
                else
                {
                    if (reader.Read())
                    {
                        string testing=reader["Time"].ToString();
                        DateTime Last = DateTime.Parse(testing,CultureInfo.InvariantCulture);
                        if (DateTime.Now > Last)
                            return true;
                        return false;
                    }
                }
               
            }
            catch (SqlException)
            {
                return false;
            }
            finally
            {
                if (SQLConnection != null)
                    SQLConnection.Dispose();
            }
            return false;
        }
        public bool RegisterNewUserCreditCard(string name, string numberCard)
        {
            bool res = false;
            SqlConnection SQLConnection = null;

            try
            {
                SQLConnection = new SqlConnection(this.connect);
                SqlCommand cmd = SQLConnection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [CreditCard] Where Code = @numcard";

                cmd.Parameters.AddWithValue("numcard", numberCard);
                SQLConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {

                    return false;
                }
                else
                {
                    res = true;
                }
                reader.Close();

                if (res)
                {
                    SqlCommand command = SQLConnection.CreateCommand();
                    command.CommandText = "INSERT INTO [CreditCard](UserName,Code,Amount) VALUES(@Username,@code,@amount)";
                    command.Parameters.AddWithValue("Username", name);
                    command.Parameters.AddWithValue("code", numberCard);
                    command.Parameters.AddWithValue("amount", 0);
                    command.ExecuteNonQuery();
                }
             
            }
            catch (SqlException)
            {
                return false;
            }
            finally
            {
                if (SQLConnection != null)
                    SQLConnection.Dispose();
            }


            
            return true;
        }

        public float GetAmount(string card, string name)
        {
            float result = -1;
            int cardNumber = Convert.ToInt32(card, CultureInfo.InvariantCulture);
            using (SqlConnection sqlconnection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand command = sqlconnection.CreateCommand();
                    command.CommandText = "SELECT * FROM [CreditCard] where Code = @cardN AND UserName = @nam";

                    command.Parameters.AddWithValue("cardN", cardNumber);
                    command.Parameters.AddWithValue("nam", name);
                    sqlconnection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            result = Convert.ToInt64(reader["amount"].ToString().Split(' ')[0],CultureInfo.InvariantCulture);
                        }
                    }
                    reader.Close();
                }
                catch (SqlException)
                {
                    return -1;
                }
            }
            return result;
        }

        public float GetAmountByUserName(string name)
        {
            float result = -1;
            using (SqlConnection sqlconnection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand command = sqlconnection.CreateCommand();
                    command.CommandText = "SELECT amount FROM [CreditCard] where UserName = @nam";

                    command.Parameters.AddWithValue("nam", name);
                    sqlconnection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            result = Convert.ToInt64(reader["Amount"].ToString(), CultureInfo.CurrentCulture);
                        }
                    }
                    reader.Close();
                }
                catch (SqlException)
                {
                    return -1;
                }
            }
            return result;
        }

        public int Subtract(string amount, string card, string name)
        {
            int resultEnd = 1;
            float result = GetAmount(card, name);
            float money = Convert.ToInt64(amount,CultureInfo.InvariantCulture);
            int cardNumber = Convert.ToInt32(card, CultureInfo.InvariantCulture);
            if (result > -1)
            {
                if (result >= money)
                {
                    float rest = result - money;
                    SqlConnection sqlconnection = null;
                    SqlDataReader reader = null;
                    
                        try
                        {
                            sqlconnection = new SqlConnection(this.connect);
                            SqlCommand command = sqlconnection.CreateCommand();
                            command.CommandText = "SELECT * FROM [CreditCard] where Code = @cardN";
                            SqlCommand newCommand = sqlconnection.CreateCommand();
                            newCommand.CommandText = "UPDATE [CreditCard] SET Amount = @subtract Where Code = @cardNumber";

                            command.Parameters.AddWithValue("cardN", cardNumber);
                            newCommand.Parameters.AddWithValue("cardNumber", cardNumber);
                            newCommand.Parameters.AddWithValue("subtract", rest);

                            sqlconnection.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                if (reader.Read())
                                {
                                    reader.Close();
                                    newCommand.ExecuteNonQuery();
                                    resultEnd = 3;
                                }
                            }
                           // reader.Close();
                        }
                        catch (SqlException)
                        {

                        }
                        finally
                        {
                            if (sqlconnection != null)
                                sqlconnection.Dispose();
                        }
                    
                }
                else
                    resultEnd = 2;
            }
            return resultEnd;
        }
        
       /* public byte[] GetImage(string name)
        {
            SqlConnection conn = new SqlConnection(this.connect);

            // inicializar el SqlDataAdapter
            SqlDataAdapter adapter = new SqlDataAdapter("select Picture from [User] where userName='" + name + "'", conn);

            //crear una tabla para obtener los datos
            System.Data.DataTable table = new System.Data.DataTable();

            //rellenar la tabla con los datos de la BBDD
            adapter.Fill(table);
            // crear un bitmap para la imagen               
            BitmapImage bitImg = new BitmapImage();
            bitImg.BeginInit();
            System.IO.Stream ms = new System.IO.MemoryStream((byte[])table.Rows[0][0]);
            bitImg.StreamSource = ms;
            try
            {
                bitImg.EndInit();

                // asociar el bitmap a la imagen
                byte[] ret = (byte[])table.Rows[0][0];
                int size = ret.Length;
                return ret;
            }
            catch (Exception)
            { }
            return new byte[1];
        }*/

        /*
        public bool EditUser(User userReceived)
        {
            bool result = false;
            using (SqlConnection SQLconnection = new SqlConnection(this.connect))
            {
                try
                {
                    if(userReceived==null)
                        throw new ArgumentNullException("userReceived");
                    SqlCommand cmd = SQLconnection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [User] where UserName = @username";
                    SqlCommand newCmd = SQLconnection.CreateCommand();
                    newCmd.CommandText = "UPDATE [User] SET Email =  @email, Question = @secret, Answer = @answer, Password = @pass";
                    cmd.Parameters.AddWithValue("username", userReceived.Name);
                        newCmd.Parameters.AddWithValue("username", userReceived.Name);
                        newCmd.Parameters.AddWithValue("eamil", userReceived.Email);
                        newCmd.Parameters.AddWithValue("secret", userReceived.Question);
                        newCmd.Parameters.AddWithValue("answer", userReceived.Answer);
                        newCmd.Parameters.AddWithValue("pass", userReceived.Password);
                        SQLconnection.Open();
                        SqlDataReader reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            if (reader.Read())
                            {
                                newCmd.ExecuteNonQuery();
                                result = true;
                            }
                        }
                    
                }
                catch (SqlException)
                {
                    return result;
                }
            }
            return result;
        }
        */
        public User GetUserTwo(string userName, string password)
        {
            using (SqlConnection sqlConnection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand command =sqlConnection.CreateCommand();
                    command.CommandText="SELECT * FROM [USER] Where userName = @username AND Password=@password ";
                    command.Parameters.AddWithValue("username",userName);
                    command.Parameters.AddWithValue("password", password);
                    sqlConnection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    if(reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            user = new User();

                            user.Answer =reader["Answer"].ToString().Split(' ')[0];
                            user.Question = Convert.ToInt16(reader["Question"].ToString().Split(' ')[0],CultureInfo.InvariantCulture);
                            user.Password =Convert.ToString(reader["Password"],CultureInfo.InvariantCulture).Split(' ')[0];
                            user.Email = reader["Email"].ToString().Split(' ')[0];
                            user.Chips = Convert.ToInt64(reader["Chips"].ToString().Split(' ')[0],CultureInfo.InvariantCulture);
                            user.Status = Convert.ToInt16(reader["Status"].ToString().Split(' ')[0],CultureInfo.InvariantCulture);
                            user.Name = Convert.ToString(userName,CultureInfo.InvariantCulture).Split(' ')[0];
                            user.UserLevel = Convert.ToInt16(reader["UserLevel"].ToString().Split(' ')[0], CultureInfo.InvariantCulture);
                            user.TimeWon = Convert.ToInt64(reader["TimeWon"].ToString().Split(' ')[0],CultureInfo.InvariantCulture);
                            return user;
                        }
                        else
                            return null;
                    }
                    reader.Close();
                }
                catch(SqlException)
                {
                    return user;
                }
                return user;
            }
        }
        
        public User GetUser(string userName)
        {
            
                SqlConnection SQL_Connection =null;
                User userReturn = new User();
                try
                {
                    SQL_Connection= new SqlConnection(this.connect);
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [User] Where userName = @username";

                    cmd.Parameters.AddWithValue("username", userName);
                    SQL_Connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            userReturn.Answer = reader["Answer"].ToString().Split(' ')[0];
                            userReturn.Question = Convert.ToInt16(reader["Question"].ToString(), CultureInfo.InvariantCulture);
                            userReturn.Password = reader["Password"].ToString().Split(' ')[0];
                            userReturn.Email = reader["Email"].ToString().Split(' ')[0];
                            userReturn.Name = userName;
                            reader.Close();

                            return userReturn;
                        }
                    }

                }
                catch (SqlException)
                {
                    return null;
                }
                finally
                {
                    if (SQL_Connection != null)
                    {
                        SQL_Connection.Dispose();
                    }
                }
                return null ;
        }
        public bool ChangeStatus(string userName,string userRequest,string newStatus)
        {
            bool exit = true;
            
            using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [Contacts] Where userName = @username and UserRequest=@userrequest";
                    SqlCommand change = SQL_Connection.CreateCommand();
                    change.CommandText = "UPDATE [Contacts] SET Status = @status Where userName = @usernameu and UserRequest=@userrequestu";
                    change.Parameters.AddWithValue("status",newStatus);
                    change.Parameters.AddWithValue("usernameu", userName);
                    change.Parameters.AddWithValue("userrequestu", userRequest);
                    cmd.Parameters.AddWithValue("username", userName);
                    cmd.Parameters.AddWithValue("userrequest",userRequest);
                    
                    SQL_Connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            reader.Close();
                            change.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        exit = false;
                        reader.Close();
                    }
                        

                }
                catch (SqlException)
                {
                    //throw new DataAccessException("Error getting a user", ex);
                }
                return exit;
            }
        }
        public bool GetStatus(string userName)
        {
            bool result = false;
            using (SqlConnection SQLconnection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand cmd = SQLconnection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [User] Where userName = @username";

                    cmd.Parameters.AddWithValue("username", userName);
                    SQLconnection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            result = true;
                        }
                    }
                }
                catch (SqlException)
                {
                    return result;
                }
            }
            return result;
        }
        public bool ChangeStatusUser(string userName, int newStatus)
        {
            bool result = false;
            using (SqlConnection SQLconnection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand cmd = SQLconnection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [User] where userName = @username";
                    SqlCommand newCmd = SQLconnection.CreateCommand();
                    newCmd.CommandText = "UPDATE [User] SET Status =  @status where userName = @username";
                    cmd.Parameters.AddWithValue("username", userName);
                    newCmd.Parameters.AddWithValue("status", newStatus);
                    newCmd.Parameters.AddWithValue("username", userName);
                    SQLconnection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            reader.Close();
                            newCmd.ExecuteNonQuery();
                            result = true;
                        }
                    }
                }
                catch (SqlException)
                {
                    return result;
                }
            }
            return result;
        }
        public bool SendRequest(string userName,string userNameRequested)
        {
            SqlConnection SQL_Connection = null;
            SqlDataReader reader = null;
            try
            {
                SQL_Connection = new SqlConnection(this.connect);
                SqlCommand insert = SQL_Connection.CreateCommand();
                SqlCommand cmd = SQL_Connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Contacts] WHERE userName=@userrequest and UserRequest=@username";
                cmd.Parameters.AddWithValue("userrequest", userNameRequested);
                cmd.Parameters.AddWithValue("username", userName);
                SQL_Connection.Open();
                reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    if (!reader.Read())
                    {
                        insert.CommandText = "INSERT INTO [Contacts] (userName,UserRequest,Status) VALUES(@Username,@userRequest,@status)";

                        insert.Parameters.AddWithValue("username", userName);
                        insert.Parameters.AddWithValue("userRequest", userNameRequested);
                        insert.Parameters.AddWithValue("status", "Pending");
                        reader.Close();
                        insert.ExecuteNonQuery();

                        return true;
                    }
                }
                else
                {
                    if (reader.Read())
                    {
                        insert.CommandText = "UPDATE Contacts SET Status=@status";
                        insert.Parameters.AddWithValue("status", "Friends");
                        reader.Close();
                        insert.ExecuteReader();
                        return false;
                    }

                }
                return false;
            }
            catch (SqlException)
            {
                return false;
            }
            finally {
                if (SQL_Connection != null)
                {
                    SQL_Connection.Dispose();
                }
            }
            
        }
        public Collection<UserContacts> GetUserContacts(string userName)
        {
            Collection<UserContacts> users = new Collection<UserContacts>();
            UserContacts newUser = new UserContacts();
            SqlDataAdapter da = null;
            using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
            {

                DataTable dataTable = null;
                try
                {
                    da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand("SELECT * FROM [Contacts] WHERE userName=@username or UserRequest=@userrequest", SQL_Connection);
                    da.SelectCommand.Parameters.AddWithValue("userrequest", userName);
                    da.SelectCommand.Parameters.AddWithValue("username", userName);
                
                  dataTable = new DataTable();
                    dataTable.Locale = CultureInfo.InvariantCulture;

                    SQL_Connection.Open();

                    // Fill the data table with select statement's query results:
                    int recordsAffected = da.Fill(dataTable);

                    if (recordsAffected > 0)
                    {
                        foreach (DataRow dr in dataTable.Rows)
                        {
                            newUser = new UserContacts();
                            newUser.UserName = dr[0].ToString().Split(' ')[0];
                            newUser.UserRequest = (string)dr[1].ToString().Split(' ')[0];
                            newUser.Status = (string)dr[2].ToString().Split(' ')[0];
                            users.Add(newUser);
                        }
                    }
                }
                catch (SqlException)
                {
                    return users;
                }
                finally
                {
                    if (da != null)
                        da.Dispose();
                    if (dataTable != null)
                    {
                        dataTable.Dispose();
                    }
                }
                return users;
            }
        }
        public DateTime ActualHour(string name)
        {
            using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [User] where userName = @username AND Banned=@status";
                    cmd.Parameters.AddWithValue("username", name);
                    cmd.Parameters.AddWithValue("status", "True");
                    SQL_Connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            DateTime time = Convert.ToDateTime(reader["RemainingTime"], CultureInfo.InvariantCulture);
                            reader.Close();
                            return time;
                        }
                    }
                   
                }
                catch (SqlException)
                {
                    return DateTime.Now;
                }
                return DateTime.Now;
            }
        
        }
        public bool IBanned(string name)
        {
            using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [User] where userName = @username AND Banned=@status";
                    cmd.Parameters.AddWithValue("username", name);
                    cmd.Parameters.AddWithValue("status", "True");
                    SQL_Connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            DateTime time = Convert.ToDateTime(reader["RemainingTime"],CultureInfo.InvariantCulture);
                            reader.Close();

                            if (time < DateTime.Now)
                            {
                                BanUser(name, "False", DateTime.Now);
                                return false;
                            }
                            return true;
                        }
                    }
                }
                catch (SqlException)
                {
                    return false;
                }
                return false;
            }
        }
        public bool BanUser(string name,string ban,DateTime time)
        {
            
           
            using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "UPDATE [User] SET Banned =  @status,RemainingTime=@remaining where userName = @username";
                    cmd.Parameters.AddWithValue("username", name);
                    cmd.Parameters.AddWithValue("status", ban);
               
                    cmd.Parameters.AddWithValue("remaining",time);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }

            }

        }

        




        public Collection<User> GetAllUsers
        {
            get
            {
                Collection<User> users = new Collection<User>();
                User userReceived = new User();
                SqlDataAdapter da = null;

                using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
                {

                    string cmd = "SELECT * FROM [User] ORDER BY userName";


                    DataTable dataTable = null;
                    try
                    {
                        da = new SqlDataAdapter(cmd, SQL_Connection);

                        dataTable = new DataTable();

                        dataTable.Locale = CultureInfo.InvariantCulture;

                        SQL_Connection.Open();

                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);

                        if (recordsAffected > 0)
                        {
                            foreach (DataRow dr in dataTable.Rows)
                            {
                                if (dr[10].ToString() == "False")
                                {
                                    userReceived = new User();
                                    userReceived.Name = dr[0].ToString().Split(' ')[0];
                                    userReceived.Email = dr[1].ToString().Split(' ')[0];
                                    userReceived.Password = Convert.ToInt64(dr[2].ToString(), CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture);
                                    userReceived.Question = Convert.ToInt16(dr[3], CultureInfo.InvariantCulture);
                                    userReceived.Answer = Convert.ToInt32(dr[4].ToString(), CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture);
                                    //TODO : user.image = (BitmapImage)dr[5];
                                    userReceived.Status = Convert.ToInt16(dr[6], CultureInfo.InvariantCulture);
                                    userReceived.Chips = Convert.ToInt64(dr[7], CultureInfo.InvariantCulture);
                                    userReceived.UserLevel = Convert.ToInt16(dr[8], CultureInfo.InvariantCulture);
                                    userReceived.TimeWon = Convert.ToInt64(dr[9], CultureInfo.InvariantCulture);
                                    userReceived.LogOn = Convert.ToInt64(dr[12], CultureInfo.InvariantCulture);
                                    users.Add(userReceived);
                                }
                            }
                        }
                    }
                    catch (SqlException)
                    {


                        return users;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }

                }
                return users;
            }
        }

        public Collection<User> GetLogUser
        {
            get
            {
                Collection<User> users = new Collection<User>();
                User userReceived = new User();
                SqlDataAdapter da = null;

                using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
                {

                    string cmd = "SELECT * FROM [User] ORDER BY LogIn DESC,UserLevel DESC,Chips DESC,UserName DESC";


                    DataTable dataTable = null;
                    try
                    {
                        da = new SqlDataAdapter(cmd, SQL_Connection);

                        dataTable = new DataTable();

                        dataTable.Locale = CultureInfo.InvariantCulture;

                        SQL_Connection.Open();

                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);

                        if (recordsAffected > 0)
                        {
                            foreach (DataRow dr in dataTable.Rows)
                            {
                                    userReceived = new User();
                                    userReceived.Name = dr[0].ToString().Split(' ')[0];
                                    userReceived.Email = dr[1].ToString().Split(' ')[0];
                                    userReceived.Password = Convert.ToInt64(dr[2].ToString(), CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture);
                                    userReceived.Question = Convert.ToInt16(dr[3], CultureInfo.InvariantCulture);
                                    userReceived.Answer = Convert.ToInt32(dr[4].ToString(), CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture);
                                    //TODO : user.image = (BitmapImage)dr[5];
                                    userReceived.Status = Convert.ToInt16(dr[6], CultureInfo.InvariantCulture);
                                    userReceived.Chips = Convert.ToInt64(dr[7], CultureInfo.InvariantCulture);
                                    userReceived.UserLevel = Convert.ToInt16(dr[8], CultureInfo.InvariantCulture);
                                    userReceived.TimeWon = Convert.ToInt64(dr[9], CultureInfo.InvariantCulture);
                                    userReceived.LogOn = Convert.ToInt64(dr[12], CultureInfo.InvariantCulture);
                                    users.Add(userReceived);
                               
                            }
                        }
                    }
                    catch (SqlException)
                    {


                        return users;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }

                }
                return users;
            }
        }


        public Collection<User> GetAllBannedUsers
        {
            get
            {
                Collection<User> users = new Collection<User>();
                User userReceived = new User();
                SqlDataAdapter da = null;

                using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
                {

                    string cmd = "SELECT * FROM [User] ORDER BY userName";


                    DataTable dataTable = null;
                    try
                    {
                        da = new SqlDataAdapter(cmd, SQL_Connection);

                        dataTable = new DataTable();

                        dataTable.Locale = CultureInfo.InvariantCulture;

                        SQL_Connection.Open();

                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);

                        if (recordsAffected > 0)
                        {
                            foreach (DataRow dr in dataTable.Rows)
                            {
                                if (dr[10].ToString() == "True")
                                {
                                    userReceived = new User();
                                    userReceived.Name = dr[0].ToString().Split(' ')[0];
                                    userReceived.Email = dr[1].ToString().Split(' ')[0];
                                    userReceived.Password = Convert.ToInt64(dr[2].ToString(), CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture);
                                    userReceived.Question = Convert.ToInt16(dr[3], CultureInfo.InvariantCulture);
                                    userReceived.Answer = Convert.ToInt32(dr[4].ToString(), CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture);
                                    //TODO : user.image = (BitmapImage)dr[5];
                                    userReceived.Status = Convert.ToInt16(dr[6], CultureInfo.InvariantCulture);
                                    userReceived.Chips = Convert.ToInt64(dr[7], CultureInfo.InvariantCulture);
                                    userReceived.UserLevel = Convert.ToInt16(dr[8], CultureInfo.InvariantCulture);
                                    userReceived.TimeWon = Convert.ToInt64(dr[9], CultureInfo.InvariantCulture);

                                    users.Add(userReceived);
                                }
                            }
                        }
                    }
                    catch (SqlException)
                    {


                        return users;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }

                }
                return users;
            }
        }
        public void IncrementLog(string userReceived)
        {
            using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "UPDATE [User] SET LogIn =  (LogIn+1) where UserName=@name";
                    cmd.Parameters.AddWithValue("name", userReceived);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                                    }
                catch (SqlException)
                {
                    
                }

            }

        }
        public string RegisterNewUser(string userReceived, string mail, string password, string question, string answer,byte[] picture)
        {
            picture = new byte[1];
            bool exit = true;
            int chip = 20000;
            int stat = 0;
            string level = "1";
            string timeWon = "0";
            int logOn = 0;
            string banned = "False";
            SqlConnection SQL_Connection = null;
            
                try
                {
                    SQL_Connection = new SqlConnection(this.connect);
            
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [User] Where userName = @username";

                    cmd.Parameters.AddWithValue("username", userReceived);
                    SQL_Connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        return "00";
                    }
                    reader.Close();

                    if (exit)
                    {
                        SqlCommand command = SQL_Connection.CreateCommand();
                        command.CommandText = "INSERT INTO [User](userName,Email,Password,Question,Answer,Picture,Status,Chips,UserLevel,TimeWon,Banned,RemainingTime,LogIn) VALUES(@Username,@Mail,@Pass,@question,@answer,@pic,@stat,@chip,@level,@timeWon,@ban,@re,@log)";
                        command.Parameters.AddWithValue("Username", userReceived);
                        command.Parameters.AddWithValue("Mail", mail);
                        command.Parameters.AddWithValue("Pass", password);
                        command.Parameters.AddWithValue("question", question);
                        command.Parameters.AddWithValue("answer", answer);
                        command.Parameters.AddWithValue("pic", picture);
                        command.Parameters.AddWithValue("stat", stat);
                        command.Parameters.AddWithValue("chip", chip);
                        command.Parameters.AddWithValue("level", level);
                        command.Parameters.AddWithValue("timeWon", timeWon);
                        command.Parameters.AddWithValue("ban", banned);
                        command.Parameters.AddWithValue("re", DateTime.Now);
                        command.Parameters.AddWithValue("log", logOn);
                        
                        command.ExecuteNonQuery();
                    }
                   
                }
                catch (SqlException)
                {
                    return "00";
                }
                finally
                {
                    if (SQL_Connection != null )
                    {
                        SQL_Connection.Dispose();
                    }

                }
            
            return "01";
        }
        public Collection<LevelDatabase> GetAllLevels
        {
            get
            {
                Collection<LevelDatabase> levels=null;
                LevelDatabase level = new LevelDatabase();
                SqlDataAdapter da = null;

                using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
                {

                    string cmd = "SELECT * FROM [Level]";


                    DataTable dataTable = null;
                    try
                    {
                        da = new SqlDataAdapter(cmd, SQL_Connection);

                        dataTable = new DataTable();

                        dataTable.Locale = CultureInfo.InvariantCulture;

                        SQL_Connection.Open();

                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);

                        if (recordsAffected > 0)
                        {
                            levels = new Collection<LevelDatabase>();
                            foreach (DataRow dr in dataTable.Rows)
                            {
                                level = new LevelDatabase();
                                level.Level = Convert.ToInt32(dr[0],CultureInfo.InvariantCulture);
                                level.TimeWon = Convert.ToInt64(dr[1], CultureInfo.InvariantCulture);
                                level.Chips = Convert.ToInt64(dr[2], CultureInfo.InvariantCulture);
                                levels.Add(level);
                            }
                        }
                    }
                    catch (SqlException)
                    {
                        return levels;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }

                }
                return levels;
            }
        }

        public Collection<User> GetAllHands
        {
            get
            {
                Collection<User> users = new Collection<User>();
                User userReceived = new User();
                SqlDataAdapter da = null;

                using (SqlConnection SQL_Connection = new SqlConnection(this.connect))
                {

                    string cmd = "SELECT * FROM [Hands] ORDER BY Amount DESC";


                    DataTable dataTable = null;
                    try
                    {
                        da = new SqlDataAdapter(cmd, SQL_Connection);

                        dataTable = new DataTable();

                        dataTable.Locale = CultureInfo.InvariantCulture;

                        SQL_Connection.Open();

                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);

                        if (recordsAffected > 0)
                        {
                            foreach (DataRow dr in dataTable.Rows)
                            {
                                userReceived = new User();
                                userReceived.Name = dr[0].ToString().Split(' ')[0];
                                userReceived.Chips = Convert.ToInt64(dr[1], CultureInfo.InvariantCulture);
                                
                                users.Add(userReceived);
                            }
                        }
                    }
                    catch (SqlException)
                    {
                        return users;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }

                }
                return users;
            }

        }

        public string NewHand(string name, long amount)
        {
            SqlConnection SQL_Connection = null;
            {
                try
                {
                    SQL_Connection = new SqlConnection(this.connect);
                    SqlCommand command = SQL_Connection.CreateCommand();
                    command.CommandText = "INSERT INTO [Hands](UserName,Amount) VALUES(@Username,@amount)";
                    command.Parameters.AddWithValue("Username", name);
                    command.Parameters.AddWithValue("amount", amount);
                    SQL_Connection.Open();

                    command.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    return "00";
                }
                finally
                {
                    if (SQL_Connection != null)
                    {
                        SQL_Connection.Dispose();
                    }

                }
            }
            return "01";

        }

        public long BestHand(string name)
        {
            long Result = 0;
            SqlConnection SQLConnection = null;
            try
            {
                SQLConnection = new SqlConnection(this.connect);
                SqlCommand command = SQLConnection.CreateCommand();
                command.CommandText = "SELECT MAX(Amount) FROM [Hands] GROUP BY UserName HAVING UserName = @name";
                command.Parameters.AddWithValue("@name", name);
                SQLConnection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    if (reader.Read())
                    {
                        Result = Convert.ToInt64(reader[0].ToString(), CultureInfo.InvariantCulture);
                    }
                }
            }
            catch (SqlException)
            {
                Result = -1;
            }
            finally
            {
                if (SQLConnection != null)
                {
                    SQLConnection.Close();
                }
            }
            return (Result);
        }
    }
}
